package carassius.DAL;

import carassius.BLL.Events.Animal.AnimalAddedListener;
import carassius.BLL.Events.Animal.AnimalAddedPublisher;
import carassius.BLL.Events.Animal.AnimalRemovedListener;
import carassius.BLL.Events.Animal.AnimalRemovedPublisher;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author siebz0r
 */
public class AnimalTable
		extends OrganismTable<AnimalRow>
		implements AnimalAddedPublisher,
				   AnimalRemovedPublisher
{
	//<editor-fold defaultstate="collapsed" desc="Fields">
	private final ArrayList<AnimalAddedListener> _animalAddedListeners;
	private final ArrayList<AnimalRemovedListener> _animalRemovedListeners;
	private final String SQL_ADD = "INSERT INTO animal VALUES ( ? , ? , ? , ? , ? , ? , ? , ? )",
			SQL_DELETE = "DELETE FROM animal WHERE id = ?",
			SQL_GET_ALL = "SELECT * FROM organism NATURAL JOIN animal",
			SQL_GET_BY_ID = "SELECT * FROM organism NATURAL JOIN animal WHERE id = ?",
			SQL_GET_BY_AQUARIUM_ID = "SELECT * FROM organism NATURAL JOIN animal WHERE id in (SELECT organism_id FROM organism_list WHERE aquarium_id = ?)";
	private boolean _allCached;
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Constructors">
	public AnimalTable()
	{
		_animalAddedListeners = new ArrayList<AnimalAddedListener>();
		_animalRemovedListeners = new ArrayList<AnimalRemovedListener>();
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Public Methods">
	//<editor-fold defaultstate="collapsed" desc="SQL add">
	public AnimalRow add(String scientificName, String alternativeName,
						   String description, Integer minTemperature,
						   Integer maxTemperature, Double maxSize,
						   PictureRow pictureRow, String feed,
						   Integer aquariumLength, Integer aquariumHeight,
						   Integer aquariumLiter, String breed, String origin,
						   Boolean schooling) throws SQLException
	{
		return add(scientificName, alternativeName, description, minTemperature,
				maxTemperature, maxSize, (pictureRow != null) ? pictureRow.getId() : null,
				feed, aquariumLength, aquariumHeight, aquariumLiter, breed, origin,
				schooling);
	}

	public AnimalRow add(String scientificName, String alternativeName,
						   String description, Integer minTemperature,
						   Integer maxTemperature, Double maxSize,
						   Integer pictureId, String feed,
						   Integer aquariumLength, Integer aquariumHeight,
						   Integer aquariumLiter, String breed, String origin,
						   Boolean schooling) throws SQLException
	{
		Integer id = add(new Object[]
							  {
								  scientificName, alternativeName, description, minTemperature,
								  maxTemperature, maxSize, pictureId, feed, aquariumLength,
								  aquariumHeight, aquariumLiter, breed, origin, schooling
							  });
		if (id >= 0)
		{
			return getByPrimaryKey(id);
		}
		return null;
	}

	@Override
	public Integer add(Object[] args) throws SQLException
	{
		int out = -1;
		if (args != null
				&& args.length >= 14)
		{
			synchronized (getRows())
			{
				out = super.add(args);
				if (out != -1)
				{
					PreparedStatement st = Database.prepareStatement(SQL_ADD);
					st.setInt(1, out);																	//ID
					st.setObject(2, (args[7] instanceof String) ? args[7] : null, Types.VARCHAR);		//feed
					st.setObject(3, (args[8] instanceof Integer) ? args[8] : null, Types.INTEGER);	//aquarium length
					st.setObject(4, (args[9] instanceof Integer) ? args[9] : null, Types.INTEGER);	//aquarium height
					st.setObject(5, (args[10] instanceof Integer) ? args[10] : null, Types.INTEGER);	//aquarium liter
					st.setObject(6, (args[11] instanceof String) ? args[11] : null, Types.VARCHAR);		//breed
					st.setObject(7, (args[12] instanceof String) ? args[12] : null, Types.VARCHAR);		//origin
					st.setObject(8, (args[13] instanceof Boolean) ? args[13] : null, Types.BOOLEAN);	//schooling

					try
					{
						if (st.executeUpdate() == 1)
						{
							_allCached = false;
							AnimalRow animalRow = getByPrimaryKey(out);
							_allCached = true;
							fireAnimalAddedEvent(animalRow);
						}
						else
						{
							out = -1;
						}
					}
					catch (SQLException ex)
					{
						throw ex;
					}
					finally
					{
						st.close();
					}
				}
			}
		}
		return out;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL delete">
	@Override
	public boolean delete(Integer id) throws SQLException
	{
		boolean result = false;
		if (id != null && id >= 0)
		{
			PreparedStatement st = Database.prepareStatement(SQL_DELETE);
			st.setInt(1, id);

			synchronized (getRows())
			{
				try
				{
					if (st.executeUpdate() == 1
							&& super.delete(id))
					{
						result = true;
						if (getRows() != null && !getRows().isEmpty())
						{
							AnimalRow animalRow = getRows().remove(id);
							if (animalRow != null)
							{
								fireAnimalRemovedEvent(animalRow);
							}
						}
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return result;
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="SQL get">
	@Override
	public Collection<AnimalRow> getAll() throws SQLException
	{
		PreparedStatement st = Database.prepareStatement(SQL_GET_ALL);

		synchronized (getRows())
		{
			if (!_allCached)
			{
				try
				{
					ResultSet query = st.executeQuery();

					while (query.next())
					{
						AnimalRow animalRow = new AnimalRow(query);
						put(animalRow);
					}
					_allCached = true;
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
			return getRows().values();
		}
	}

	@Override
	public AnimalRow getByPrimaryKey(Integer id) throws SQLException
	{
		AnimalRow row = null;
		if (id >= 0)
		{
			synchronized (getRows())
			{
				row = getRows().get(id);
				if (row != null || _allCached)
				{
					return row;
				}

				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_ID);
				st.setInt(1, id);

				try
				{
					ResultSet query = st.executeQuery();

					if (query.next())
					{
						row = new AnimalRow(query);
						put(row);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return row;
	}

	@Override
	public ArrayList<AnimalRow> getByAquariumId(int id) throws SQLException
	{
		ArrayList<AnimalRow> out = new ArrayList<AnimalRow>();

		if (_allCached)
		{
			for (OrganismListRow organismListRow : Database.getOrganismListTable().getByAquariumId(id))
			{
				out.add(getByPrimaryKey(organismListRow.getOrganismId()));
			}
		}
		else
		{
			synchronized (getRows())
			{
				PreparedStatement st = Database.prepareStatement(SQL_GET_BY_AQUARIUM_ID);
				st.setInt(1, id);
				try
				{
					ResultSet query = st.executeQuery();
					while (query.next())
					{
						AnimalRow animalRow = new AnimalRow(query);
						put(animalRow);
						out.add(animalRow);
					}
				}
				catch (SQLException ex)
				{
					throw ex;
				}
				finally
				{
					st.close();
				}
			}
		}
		return out;
	}
	//</editor-fold>
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Events">
	//<editor-fold defaultstate="collapsed" desc="Added">
	@Override
	public void addAnimalAddedEventListener(AnimalAddedListener listener)
	{
		synchronized (_animalAddedListeners)
		{
			_animalAddedListeners.add(listener);
		}
	}

	@Override
	public void removeAnimalAddedEventListener(AnimalAddedListener listener)
	{
		synchronized (_animalAddedListeners)
		{
			_animalAddedListeners.remove(listener);
		}
	}

	private void fireAnimalAddedEvent(AnimalRow row)
	{
		synchronized (_animalAddedListeners)
		{
			for (AnimalAddedListener listener : _animalAddedListeners)
			{
				listener.animalAdded(row);
			}
		}
	}
	//</editor-fold>

	//<editor-fold defaultstate="collapsed" desc="Removed">
	@Override
	public void addAnimalRemovedEventListener(AnimalRemovedListener listener)
	{
		synchronized (_animalRemovedListeners)
		{
			_animalRemovedListeners.add(listener);
		}
	}

	@Override
	public void removeAnimalRemovedEventListener(AnimalRemovedListener listener)
	{
		synchronized (_animalRemovedListeners)
		{
			_animalRemovedListeners.remove(listener);
		}
	}

	private void fireAnimalRemovedEvent(AnimalRow row)
	{
		synchronized (_animalRemovedListeners)
		{
			for (AnimalRemovedListener listener : _animalRemovedListeners)
			{
				listener.animalRemoved(row);
			}
		}
	}
	//</editor-fold>
	//</editor-fold>
}
